import sqlite3
from datetime import datetime

DB_PATH = "./datasets/myproject.db"

def get_conn():
    return sqlite3.connect(DB_PATH)

def init_db():
    conn = get_conn()
    c = conn.cursor()
    c.execute("""
    CREATE TABLE IF NOT EXISTS 员工 (
        emp_id TEXT PRIMARY KEY,
        name TEXT,
        dept TEXT,
        note TEXT,
        created_at TEXT,
        sample_count INTEGER DEFAULT 0
    )
    """)
    # 考勤表
    c.execute("""
     CREATE TABLE IF NOT EXISTS 考勤(
         id INTEGER PRIMARY KEY AUTOINCREMENT,
         emp_id TEXT,
         name TEXT,
         dept TEXT,
         date TEXT,
         sign_in TEXT,
         sign_out TEXT,
         duration TEXT
     )
     """)

    conn.commit()
    conn.close()

def add_employee(emp_id, name, dept, note):
    try:
        conn = get_conn()
        c = conn.cursor()
        c.execute("INSERT INTO 员工 (emp_id,name,dept,note,created_at) VALUES (?,?,?,?,?)",
                  (emp_id,name,dept,note,datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
        conn.commit()
        conn.close()
        return True,"员工信息已保存"
    except sqlite3.IntegrityError:
        return False,"工号已存在"

def get_all_employees():
    conn = get_conn()
    c = conn.cursor()
    c.execute("SELECT emp_id,name,dept,note,created_at,sample_count FROM 员工")
    data = c.fetchall()
    conn.close()
    return data

def delete_employee(emp_id):
    conn = get_conn()
    c = conn.cursor()
    c.execute("DELETE FROM 员工 WHERE emp_id=?", (emp_id,))
    conn.commit()
    conn.close()
    return True,"已删除员工"

def update_employee(emp_id, note=None, sample_count=None):
    conn = get_conn()
    c = conn.cursor()
    if note is not None:
        c.execute("UPDATE 员工 SET note=? WHERE emp_id=?", (note,emp_id))
    if sample_count is not None:
        c.execute("UPDATE 员工 SET sample_count=? WHERE emp_id=?", (sample_count,emp_id))
    conn.commit()
    conn.close()
    return True,"更新成功"

def update_sample_count(emp_id, sample_count):
    """
    更新员工的 sample_count 字段
    """
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute(
        "UPDATE 员工 SET sample_count=? WHERE emp_id=?",
        (sample_count, emp_id)
    )
    conn.commit()
    conn.close()

# 签到
def save_sign_in(emp_id, name, dept):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    today = datetime.now().strftime("%Y-%m-%d")
    now = datetime.now().strftime("%H:%M:%S")

    # 如果今天已经签到过，就不重复插入
    c.execute("SELECT id FROM 考勤 WHERE emp_id=? AND date=?", (emp_id, today))
    row = c.fetchone()
    if row:
        conn.close()
        return False, "已签到"

    c.execute(
        "INSERT INTO 考勤(emp_id,name,dept,date,sign_in) VALUES(?,?,?,?,?)",
        (emp_id, name, dept, today, now)
    )
    conn.commit()
    conn.close()
    return True, "签到成功"


# 签退 + 时长
def save_sign_out(emp_id):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    today = datetime.now().strftime("%Y-%m-%d")
    now = datetime.now().strftime("%H:%M:%S")

    # 找到今天的签到记录
    c.execute("SELECT id, sign_in FROM 考勤 WHERE emp_id=? AND date=?", (emp_id, today))
    row = c.fetchone()
    if not row:
        conn.close()
        return False, "今天没有签到记录"

    record_id, sign_in_time = row
    # 计算时长
    sign_in_dt = datetime.strptime(f"{today} {sign_in_time}", "%Y-%m-%d %H:%M:%S")
    sign_out_dt = datetime.strptime(f"{today} {now}", "%Y-%m-%d %H:%M:%S")
    duration = str(sign_out_dt - sign_in_dt)

    # 更新记录
    c.execute(
        "UPDATE 考勤 SET sign_out=?, duration=? WHERE id=?",
        (now, duration, record_id)
    )
    conn.commit()
    conn.close()
    return True, "签退成功"


def get_attendance(date):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT id, emp_id, name, dept, date, sign_in, sign_out, duration FROM 考勤 WHERE date=?", (date,))
    rows = c.fetchall()
    conn.close()
    return rows